#!/Users/yanxu/jython2.2.1/jython
# encoding: utf-8
"""
poi.py

Created by yanxu on 2008-04-09.
Copyright (c) 2008 sina. All rights reserved.
"""

from org.apache.poi.hssf.usermodel import *
from org.apache.poi.hssf.usermodel.contrib import HSSFCellUtil
from org.apache.poi.hssf.util import HSSFColor
from java.io import File
from java.io import FileInputStream
from java.io import FileOutputStream
from java.io import ByteArrayOutputStream

from java.sql import Connection
from java.sql import DriverManager
from java.sql import PreparedStatement
from java.sql import ResultSet
from java.sql import SQLException
from java.sql import Statement
from java.util import ArrayList
from java.util import List
from java.lang import *

class POI(object):
    """POI for Python
    data struct:
        [-------workbook
            [-------sheet
                ({v:1,color:'red'},{v:2},{v:3}),-----row
                ({v:1},{v:2},{v:3}),-----------------row
            ],
        ]
    """
    def __init__(self,fname,output,data={}):
        super(POI, self).__init__()
        self.fname = fname
        self.data = data
        self.output = output
        self.wb = HSSFWorkbook(FileInputStream(fname))
    def loadPicture(self,imageFile):
        try:
            bos = ByteArrayOutputStream()
            filein = FileInputStream(imageFile)
            c = filein.read()
            while c!= -1:
                bos.write(c)
                c = filein.read()
            filein.close();
            picIndex = self.wb.addPicture(bos.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG)
            return picIndex
        except:
            return 0
    
        #设置格式
    def setformat(self,c,cell):
        cellStyle = self.wb.createCellStyle()
        
        #设置边框
        if cell.get("border","")==True:
            cellStyle.setBorderTop(cellStyle.BORDER_THIN)
            cellStyle.setBorderLeft(cellStyle.BORDER_THIN)
            cellStyle.setBorderRight(cellStyle.BORDER_THIN)
            cellStyle.setBorderBottom(cellStyle.BORDER_THIN)
         #设置单元格背景色
        if cell.get('bgcolor','')!='':
            font = self.wb.createFont()
            font.setColor(HSSFColor.BLACK.index)
            cellStyle.setFont(font)
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT)
            #cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index)
            #print type(HSSFColor.LIGHT_GREEN.index),HSSFColor.LIGHT_YELLOW.index
            cellStyle.setFillForegroundColor(cell['bgcolor'])#0xC3FFC4
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
        #设置字体颜色及单元格颜色
        if cell.get('color','')!='':
            font = self.wb.createFont()
            font.setColor(HSSFColor.RED.index)
            cellStyle.setFont(font)
            #c.setCellStyle(cellStyle)
            if cell.get('bgcolor','')!='':
                cellStyle.setFont(font)
                cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT)
                #cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index)
                cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index)#0xC3FFC4
                cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
        #单元格添加公式
        if cell.get('builtinFormats','')!='':
            cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(cell['builtinFormats']))
        c.setCellStyle(cellStyle)
        return c
    def save(self):
        """save excel file"""
        for i,data in enumerate(self.data):
            sheet=self.wb.getSheetAt(i)
            partiarch = sheet.createDrawingPatriarch()#创建图片所需
            for rindex,row in enumerate(data):
                r = sheet.createRow(rindex)
                for cindex,cell in enumerate(row):
                    c = r.createCell(cindex)
                    print cell
                    s=cell['v']
                    if s=='':continue
                    #print s
                    if type(s)==dict:
                        anchor = HSSFClientAnchor(0, 0, 0, 0, s['x'], s['y'], s['x']+s['width'], s['y']+s['height'])
                        #anchor = HSSFClientAnchor(0, 0, 0, 0, 10, 10, 16, 24)
                        anchor.setAnchorType(2)
                        partiarch.createPicture(anchor, self.loadPicture(s['fname']))
                    elif type(s)==int:
                        c.setCellType(HSSFCell.CELL_TYPE_NUMERIC)
                        c.setCellValue(s)
                    else:
                        if s.find("=")==0:
                            s=s[1:]
                            c.setCellFormula(s)
                        else:
                            c.setCellValue(HSSFRichTextString(s))
                    c = self.setformat(c,cell)
        #cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        self.__saveFile()
    def __saveFile(self):
        fileOut=FileOutputStream(self.output)
        self.wb.write(fileOut)
        fileOut.close()


def main():
    import datetime,os
    data=[
        [
            ({'v':'1','color':'red'},{'v':u'中文'},{'v':u'ABCD'}),
            ({'v':'1'},{'v':'222'},{'v':'123123'}),
        ],
    ]
    output='report_%s.xls'%datetime.date.today()
    fname='blank.xls'
    print fname
    poi=POI(fname,output,data)
    poi.save()
    
if __name__ == '__main__':
    main()


